import { unstable_noStore as noStore } from 'next/cache';
import prisma from './prisma';
import { formatCurrency } from './utils';

export async function fetchRevenue() {

    noStore();
    try {
        const data = await prisma.revenue.findMany()
        return data;
    } catch (error) {
        console.error('Database Error:', error);
        throw new Error('Failed to fetch revenue data.');
    }
}

// 最新的5条数据
export async function fetchLatestInvoices() {
    noStore();
    try {

        const data = await prisma.invoice.findMany({
            select: {
                id: true,
                amount: true,
                customer: {
                    select: {
                        name: true,
                        image_url: true,
                        email: true,
                    }
                }
            },
            orderBy: {
                date: 'desc'
            },
            take: 5,
        })

        const latestInvoices = data.map((invoice: any) => ({
            ...invoice,
            ...invoice.customer,
            amount: formatCurrency(invoice.amount),

        }));
        // console.log(latestInvoices, 'latestInvoices')
        return latestInvoices;
    } catch (error) {
        console.error('Database Error:', error);
        throw new Error('Failed to fetch the latest invoice.');
    }
}

export async function fetchCardData() {
    noStore();
    try {

        let flag = false
        if (flag) {
            const invoiceGroup = await prisma.invoice.groupBy({
                by: ['status'],
                _sum: {
                    amount: true,
                },
            })

            console.log(invoiceGroup, 'invoiceGroup')
        }


        const invoiceCountPromise = prisma.invoice.count();
        const customerCountPromise = prisma.customer.count();

        const invoiceStatusPromise = prisma.$queryRaw`SELECT
           SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS "paid",
           SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS "pending"
           FROM invoice`;

        const data = await Promise.all([
            invoiceCountPromise,
            customerCountPromise,
            invoiceStatusPromise,
        ]);

        const numberOfInvoices = Number(data[0] ?? '0');
        const numberOfCustomers = Number(data[1] ?? '0');
        const totalPaidInvoices = formatCurrency(data[2][0].paid ?? '0');
        const totalPendingInvoices = formatCurrency(data[2][0].pending ?? '0');

        return {
            numberOfCustomers,
            numberOfInvoices,
            totalPaidInvoices,
            totalPendingInvoices,
        };
    } catch (error) {
        console.error('Database Error:', error);
        throw new Error('Failed to fetch card data.');
    }
}

const ITEMS_PER_PAGE = 6;
// 按查询条件查询订单
export async function fetchFilteredInvoices(
    query: string,
    currentPage: number,
) {
    noStore();
    const offset = (currentPage - 1) * ITEMS_PER_PAGE;

    try {
        let bySql = true
        if (bySql) {
            // 原始方法
            const likeVaule = `%${query}%`;
            const invoice = await prisma.$queryRaw`
                SELECT
                  invoice.id,
                  invoice.amount,
                  invoice.date,
                  invoice.status,
                  customer.name,
                  customer.email,
                  customer.image_url
                FROM invoice
                JOIN customer ON invoice.customer_id = customer.id
                WHERE
                  customer.name LIKE ${likeVaule} OR
                  customer.email LIKE ${likeVaule} OR
                  invoice.date LIKE ${likeVaule}
                ORDER BY invoice.date DESC
                LIMIT ${offset},${ITEMS_PER_PAGE}
              `;
            console.log(invoice)
            return invoice
        } else {
            const invoice = await prisma.invoice.findMany({
                where: {
                    OR: [
                        {
                            date: {
                                contains: query
                            }
                        },
                        {
                            customer: {
                                name: {
                                    contains: query
                                }
                            }
                        },
                        {
                            customer: {
                                email: {
                                    contains: query
                                }
                            }
                        },
                    ]
                },
                select: {
                    id: true,
                    amount: true,
                    date: true,
                    status: true,
                    customer: {
                        select: {
                            name: true,
                            email: true,
                            image_url: true
                        },
                    }
                },
                take: ITEMS_PER_PAGE,
                skip: offset,
                orderBy: {
                    date: 'desc'
                }
            })
            return invoice.map(({ customer, ...ohters }: any) => {
                return {
                    ...ohters,
                    ...customer
                }
            });
        }
    } catch (error) {
        console.error('Database Error:', error);
        throw new Error('Failed to fetch invoice.');
    }
}

// 查询订单页数
export async function fetchInvoicesPages(query: string) {
    noStore();
    try {
        const count = await prisma.invoice.count()
        const totalPages = Math.ceil(count / ITEMS_PER_PAGE);
        return totalPages;
    } catch (error) {
        console.error('Database Error:', error);
        throw new Error('Failed to fetch total number of invoice.');
    }
}

export async function fetchInvoiceById(id: string) {
    noStore();
    try {
        const data = await prisma.invoice.findUnique({
            select: {
                id: true,
                customer_id: true,
                amount: true,
                status: true
            },
            where: {
                id: Number(id)
            }
        })
        data.amount = data.amount / 100
        return data;
    } catch (error) {
        console.error('Database Error:', error);
        throw new Error('Failed to fetch invoice.');
    }
}

export async function fetchCustomers() {
    try {

        const data = await prisma.customer.findMany({
            select: {
                id: true,
                name: true,
            },
            orderBy: {
                name: "desc"
            }
        })

        const customer = data;
        return customer;
    } catch (err) {
        console.error('Database Error:', err);
        throw new Error('Failed to fetch all customer.');
    }
}

export async function fetchFilteredCustomers(query: string) {
    noStore();
    try {
        const data = await prisma.$queryRaw`
          SELECT
            customer.id,
            customer.name,
            customer.email,
            customer.image_url,
            COUNT(invoice.id) AS total_invoices,
            SUM(CASE WHEN invoice.status = 'pending' THEN invoice.amount ELSE 0 END) AS total_pending,
            SUM(CASE WHEN invoice.status = 'paid' THEN invoice.amount ELSE 0 END) AS total_paid
          FROM customer
          LEFT JOIN invoice ON customer.id = invoice.customer_id
          GROUP BY customer.id, customer.name, customer.email, customer.image_url
          ORDER BY customer.name ASC
        `;

        const customer = data.map((customer: any) => ({
            ...customer,
            total_pending: formatCurrency(customer.total_pending),
            total_paid: formatCurrency(customer.total_paid),
        }));
        console.log(customer, '---------')
        return customer;
    } catch (err) {
        console.error('Database Error:', err);
        throw new Error('Failed to fetch customer table.');
    }
}

export async function getUser(email: string) {
    try {
        const user = await prisma.user.findUnique({
            where: {
                email
            }
        });
        return user;
    } catch (error) {
        console.error('Failed to fetch user:', error);
        throw new Error('Failed to fetch user.');
    }
}